package edu.fzu.costlog.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.fzu.costlog.domain.CostCategory;
import edu.fzu.costlog.domain.CostLog;
import edu.fzu.costlog.utils.DBUtils;

public class CostLogDaoJDBCImpl implements CostLogDao {

	private static final String SQL_ADD = "insert into cost_log(cost_desc,cost_type,cost_amount,cost_time) values(?,?,?,?)";
	private static final String SQL_LOADALL = "select * from cost_log order by log_id desc";
	private static final String SQL_DEL = "delete from cost_log where log_id=?";
	private static final String SQL_GETBYID = "select * from cost_log where log_id=?";
	private static final String SQL_UPDATE = "update cost_log set cost_desc=?,cost_type=?,cost_amount=?,cost_time=? where log_id=?";

	public void addLog(CostLog log) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_ADD);

			pstmt.setString(1, log.getLogDesc());
			pstmt.setInt(2, log.getTypeId());
			pstmt.setDouble(3, log.getCostAmount());
			pstmt.setTimestamp(4, new java.sql.Timestamp(log.getCostTime()
					.getTime()));

			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

	public List<CostLog> loadall() {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<CostLog> logList = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_LOADALL);
			rset = pstmt.executeQuery();
			logList = new ArrayList<CostLog>();

			while (rset.next()) {

				CostLog log = new CostLog();

				log.setLogId(rset.getInt("log_id"));
				log.setLogDesc(rset.getString("cost_desc"));

				log.setTypeId(rset.getInt("cost_type"));
				log.setCostType(new CostCategoryDaoJDBCImpl()
						.getCategoryById(rset.getInt("cost_type")));
				log.setCostAmount(rset.getDouble("cost_amount"));
				log.setCostTime(rset.getTime("cost_time"));

				logList.add(log);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, rset);
		}

		return logList;
	}

	public void delLog(Integer logId) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_DEL);
			pstmt.setInt(1, logId);
			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

	public CostLog getLogById(Integer logId) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		CostLog log = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_GETBYID);
			pstmt.setInt(1, logId);
			rset = pstmt.executeQuery();

			if (rset.next()) {

				log = new CostLog();

				log.setLogId(rset.getInt("log_id"));
				log.setLogDesc(rset.getString("cost_desc"));
				log.setTypeId(rset.getInt("cost_type"));
				log.setCostType(new CostCategoryDaoJDBCImpl()
						.getCategoryById(rset.getInt("cost_type")));
				log.setCostAmount(rset.getDouble("cost_amount"));
				log.setCostTime(rset.getTime("cost_time"));
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, rset);
		}

		return log;
	}

	public void updateLog(CostLog log) {

		DBUtils utils = new DBUtils();
		Connection conn = null;
		PreparedStatement pstmt = null;

		conn = utils.getConn();

		try {

			pstmt = conn.prepareStatement(SQL_UPDATE);

			pstmt.setString(1, log.getLogDesc());
			pstmt.setInt(2, log.getTypeId());
			pstmt.setDouble(3, log.getCostAmount());
			pstmt.setTimestamp(4, new java.sql.Timestamp(log.getCostTime()
					.getTime()));
			pstmt.setInt(5, log.getLogId());

			pstmt.executeUpdate();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			utils.releaseResource(conn, pstmt, null);
		}
	}

}
